﻿using System;
using System.Collections.Generic;
using System.Text;
using XHD.DBUtility;
using System.IO;

namespace XHD.Excel
{
    //Excel导入核心类
    public class Excel
    {
        private Dictionary<string, Config> configs = null;//配置文件全路径和对应的配置类键值集合
        private string excelPath = "";//excel路径（可以为目录）
        private NPOICell npoiCell = null;//Aspose.Cells封装类
        private int NewID = 0;
        private int headerIndex = 0;//列头所在行

        public Excel()
        {
            excelPath = ConfigHelper.Instance().ExcelPath;
            InitConfig();
        }

        public Excel(string excelPath)
        {
            this.excelPath = excelPath;
            ConfigHelper.Instance().ExcelPath = excelPath;
            InitConfig();
        }

        public Excel(string excelPath, int id)
        {
            this.excelPath = excelPath;
            this.NewID = id;
            ConfigHelper.Instance().ExcelPath = excelPath;
            InitConfig();
        }
        //导入操作核心方法，负责整个Excel导入
        public int Import()
        {
            if (configs.Count > 0)
            {
                int x = ExcuteImport();
                return x;
            }
            else
            {
                throw new Exception("Config对象个数为0，无法导入！");
            }
        }

        //执行Excel导入
        private int ExcuteImport()
        {
            string sqlInsert = "";//插入操作使用的sql语句
            
            int dataIndex = 1;//数据起始行
            int tcount = dataIndex;
            int rowcount = dataIndex;
            foreach (string excelFullName in configs.Keys)//遍历所有config
            {
                npoiCell = new NPOICell(excelFullName);
                List<Entity> entities = configs[excelFullName].Entities;//注意每个config中不一定只有一个实体，可以配置多个，这样每个excel可以导入到多张表中
                headerIndex = configs[excelFullName].HeaderIndex;
                dataIndex = configs[excelFullName].DataIndex;
                rowcount = npoiCell.GetRowCount();                

                while (IsEnd(npoiCell, dataIndex))
                {
                    foreach (Entity entity in entities)
                    {
                        if (entity.Propertys.Count > 0)//说明配置了Column字段
                        {
                            sqlInsert = "insert into " + entity.Name + "(" + GetSqlFieldString(npoiCell, entity) + ")" + " Values(" + GetSqlFieldValueString(npoiCell, entity, dataIndex) + ")";
                            DbHelperSQL.Query(sqlInsert);
                        }

                    }
                    dataIndex++;
                }
            }
            return rowcount - tcount;
        }

        /// <summary>
        /// 根据EndFlag标记判断当前数据行是否结束
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="endFlag"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        private bool IsEnd(NPOICell npoiCell, int row)
        {
            int rowcount = npoiCell.GetRowCount();
            return row < rowcount;
        }

        /// <summary>
        /// 根据HeaderText配置节确定列索引（从0开始）
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="headerIndex"></param>
        /// <param name="headerText"></param>
        /// <returns></returns>
        private int GetColumnIndexByHeaderText(NPOICell npoiCell, string headerText)
        {
            int columnIndex = npoiCell.GetColumnCount();
            int r = -1;

            for (int i = 0; i < columnIndex; i++)
            {
                if (npoiCell.GetCellValue(headerIndex, i) == headerText)
                {
                    r = i;
                }
            }
           
            return r;
        }

        /// <summary>
        /// 根据DefaultValue配置节确定默认值
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="entityName"></param>
        /// <param name="columnName"></param>
        /// <param name="defaultValue"></param>
        /// <returns></returns>
        private string GetDefaultValue(NPOICell npoiCell, string entityName, string columnName, string defaultValue)
        {
            string r = "";
            switch (defaultValue)
            {
                case "NewID": r = this.NewID.ToString(); break;
                case "NewTime": r = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"); break;
                default: r = defaultValue; break;
            }
            return r;
        }


        /// <summary>
        /// 得到查询字段sql语句段
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        private string GetSqlFieldString(NPOICell npoiCell, Entity entity)
        {
            string sql = "";
            foreach (Property p in entity.Propertys)
            {
                sql += p.ColumnName + ",";
            }
            sql = sql.TrimEnd(',');
            return sql;
        }


        /// <summary>
        /// 得到查询值sql语句段
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="entity"></param>
        /// <param name="headerIndex"></param>
        /// <param name="dataIndex"></param>
        /// <returns></returns>
        private string GetSqlFieldValueString(NPOICell npoiCell, Entity entity,  int dataIndex)
        {
            string sql = "";
            string v = "";
            foreach (Property p in entity.Propertys)//遍历实体的所有属性
            {
                if (p.CodeTable != null)
                {
                    sql += "'" + GetCodeTableValue(npoiCell, p, dataIndex).Replace("'", "''") + "',";//注意如果单元格本身的值就有“'”的情况
                }
                else//说明此属性是一个代码表字段
                {
                    int columnindex = GetColumnIndexByHeaderText(npoiCell, p.HeaderText);
                    if (npoiCell.IsMerged(dataIndex, columnindex))//是否为合并单元格(对于合并单元格取此合并单元格的第一个值)
                    {
                        v = npoiCell.GetMergedCellValue(dataIndex, columnindex);
                    }
                    else
                    {
                        v = npoiCell.GetCellValue(dataIndex, columnindex);
                    }
                    if (v == "")//说明单元格中没有任何值，就要考虑“默认值”和“必须”属性
                    {
                        if (GetDefaultValue(npoiCell, entity.Name, p.ColumnName, p.DefaultValue) != "")//说明有默认值
                        {
                            v = GetDefaultValue(npoiCell, entity.Name, p.ColumnName, p.DefaultValue);
                        }
                        else//如果单元格没有值并且无默认值，则检查此属性是否是必须的
                        {
                            if (!p.Required)
                            {
                                v = "";
                            }
                            else
                            {
                                throw new Exception("列\"" + p.HeaderText + "\"" + "不能为空！");
                            }
                        }
                    }

                    //属性长度检查
                    if (p.DataLength != 0 && p.DataLength != 5000)
                    {
                        if (!ValidateDataLength(v, p.DataLength))
                        {
                            throw new Exception("列\"" + p.HeaderText + "\"中存长度超过\"" + p.DataLength.ToString() + "\"的数据！");
                        }
                    }

                    //检查类型
                    if (p.DataType != "" && p.DataType != "string")
                    {
                        if (!ValidateDataType(v, p.DataType))
                        {
                            throw new Exception("列\"" + p.HeaderText + "\"中存在非\"" + p.DataType + "\"类型数据！");
                        }
                        sql += v.Replace("'", "''") + ",";
                    }
                    else
                    {
                        sql += "'" + v.Replace("'", "''") + "',";
                    }
                }
            }
            sql = sql.TrimEnd(',');
            return sql;
        }

        //数据类型校验
        private bool ValidateDataType(string value, string type)
        {
            bool r = false;
            double t = 0;
            switch (type.ToLower())
            {
                case "number":
                    r = double.TryParse(value, out t);
                    break;
                case "string":
                    r = true;
                    break;
                default: break; ;
            }
            return r;
        }

        //数据长度校验
        private bool ValidateDataLength(string value, int length)
        {
            if (value.Length > length)
            {
                return false;
            }
            else
            {
                return true;
            }
        }


        /// <summary>
        /// 得到代码表的对应值
        /// </summary>
        /// <param name="npoiCell"></param>
        /// <param name="property"></param>
        /// <param name="headerIndex"></param>
        /// <param name="dataIndex"></param>
        /// <returns></returns>
        private string GetCodeTableValue(NPOICell npoiCell, Property property, int dataIndex)
        {
            int columnindex = GetColumnIndexByHeaderText(npoiCell, property.HeaderText);
            string value = npoiCell.IsMerged(dataIndex,columnindex) ? npoiCell.GetMergedCellValue(dataIndex, columnindex) : npoiCell.GetCellValue(dataIndex, columnindex).Replace("'", "''");
            StringBuilder sb = new StringBuilder("select distinct ");
            sb.Append(property.CodeTable.PrimaryKey);
            sb.Append(" from ");
            sb.Append(property.CodeTable.Name);
            sb.Append(" where ");
            sb.Append(property.CodeTable.ReferenceColumn);
            sb.Append("='");
            sb.Append(value);
            sb.Append("'");
            if (!string.IsNullOrEmpty(property.CodeTable.Condition))
            {
                sb.Append(" and " + property.CodeTable.Condition);
            }

            if (property.DataLength != 0 && property.DataLength != 5000)
            {
                if (!ValidateDataLength(value, property.DataLength))
                {
                    throw new Exception("列\"" + property.HeaderText + "\"中存长度超过\"" + property.DataLength.ToString() + "\"的数据！");
                }
            }
            if (DbHelperSQL.GetSingle(sb.ToString()) != null)
            {
                return DbHelperSQL.GetSingle(sb.ToString()).ToString();
            }
            else
            {
                throw new Exception("没有对应的代码表值！");
            }
        }

        //初始化，主要将excel文件和配置类对应关系存放到configs对象中，方便以后遍历
        private void InitConfig()
        {
            configs = new Dictionary<string, Config>();
            List<string> excelFullNames = new List<string>();
            if (Directory.Exists(excelPath))//判断目录是否存在(注意：除了多套excel对应多套模板，还可能有一个模板对应多个excel的情况）
            {
                FileHelper.InitFileInfoList();
                List<string> excelConfigFileFullNames = FileHelper.GetFileInfo(".xml", ConfigHelper.Instance().ExcelPath, true, true);//在目录中查找所有名称中包含".xml"的文件
                if (excelConfigFileFullNames.Count == 1)//说明是一个excel对应一个xml配置文件的情况
                {
                    AddConfigsByXmlFullNameHasNothingToExcelname(excelConfigFileFullNames[0]);
                }
                else if (excelConfigFileFullNames.Count > 1)//说明目录中有多个xml文件，对应多个excel
                {
                    foreach (string excelConfigFileFullName in excelConfigFileFullNames)
                    {
                        AddConfigsByXmlFullName(excelConfigFileFullName);
                    }
                }
                else
                {
                    throw new Exception("所指定目录不包含任何XLM模板，请重新指定！");
                }

            }
            else//说明指定的不是目录而是excel文件路径
            {
                AddConfigByExcelFullName(excelPath);
            }

        }

        //根据Excel全路径构造路径和配置类对应关系（主要用于指导Excel全路径的情况）
        private void AddConfigByExcelFullName(string excelFullName)
        {
            string excelConfigFileFullName = excelFullName.Substring(0, excelFullName.LastIndexOf("\\")) + "\\" + Path.GetFileNameWithoutExtension(excelFullName) + ".xml";
            Config config = null;
            if (File.Exists(excelConfigFileFullName))
            {
                config = new Config(excelConfigFileFullName);//创建配置（Config）对象
                configs.Add(excelFullName, config);
            }
            else
            {
                throw new Exception("所指定文件没有对应的配置文件，请重新指定！" + excelConfigFileFullName);
            }
        }

        //根据XML文件全路径构造路径和配置类对应关系(得到一个xml文件对应的所有excel然后构造config对象存放到configs中)
        private void AddConfigsByXmlFullName(string xmlFullName)
        {
            string excelDirectory = xmlFullName.Substring(0, xmlFullName.LastIndexOf("\\"));//根据路径取得对应的目录
            Config config = null;
            FileHelper.InitFileInfoList();
            List<string> excelFullNames = FileHelper.GetFileInfo(Path.GetFileNameWithoutExtension(xmlFullName), excelDirectory, true, true);//根据xml的名称搜索包含此名称的文件
            if (excelFullNames.Count >= 1)
            {
                foreach (string excelFullName in excelFullNames)
                {
                    if (excelFullNames.IndexOf(".xls") != -1)//必须是excel文件(排除xml文件)
                    {
                        config = new Config(xmlFullName);
                        configs.Add(excelFullName, config);
                    }
                }
            }
            else
            {
                throw new Exception("所指定模板不包含对应的Excel文件，请重新指定！");
            }
        }

        //根据XML文件全路径构造路径和配置类对应关系（此种情况由于只有一个xml，必然对应一个或多个excel文件，所以只需要查找excel文件即可）
        private void AddConfigsByXmlFullNameHasNothingToExcelname(string xmlFullName)
        {
            Config config = null;
            string excelDirectory = xmlFullName.Substring(0, xmlFullName.LastIndexOf("\\"));
            FileHelper.InitFileInfoList();
            List<string> excelFullNames = FileHelper.GetFileInfo(".xls", excelDirectory, true, true);
            if (excelFullNames.Count >= 1)
            {
                foreach (string excelFullName in excelFullNames)
                {
                    config = new Config(xmlFullName);
                    configs.Add(excelFullName, config);
                }
            }
            else
            {
                throw new Exception("所指定模板不包含对应的Excel文件，请重新指定！");
            }
        }


    }
}
